#!/bin/bash
# 远程服务器配置
REMOTE_HOST="218.244.141.86"
REMOTE_USER="root"
REMOTE_PASSWORD="Jason040515" 
DB_NAME="recipedb"
DB_USER="postgres"

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m'

# 全局变量
CURRENT_PAGE=1
PAGE_SIZE=10
TOTAL_RECORDS=0
CURRENT_TABLE=""
CURRENT_TABLE_INFO=""

# 函数：显示错误信息并退出
error_exit() {
    echo -e "${RED}错误: $1${NC}" >&2
    exit 1
}

# 函数：显示提示信息
info() {
    echo -e "${BLUE}提示: $1${NC}"
}

# 函数：显示成功信息
success() {
    echo -e "${GREEN}成功: $1${NC}"
}

# 函数：显示警告信息
warning() {
    echo -e "${YELLOW}警告: $1${NC}"
}

# 函数：通过SSH执行远程命令
execute_remote() {
    local command="$1"
    sshpass -p "$REMOTE_PASSWORD" ssh -o StrictHostKeyChecking=no "${REMOTE_USER}@${REMOTE_HOST}" "$command"
}

# 函数：通过SSH执行数据库查询
execute_remote_query() {
    local query="$1"
    execute_remote "sudo -u postgres psql -d $DB_NAME -t -A -F',' -c \"$query\""
}

# 函数：通过SSH执行数据库命令
execute_remote_command() {
    local command="$1"
    execute_remote "sudo -u postgres psql -d $DB_NAME -c \"$command\""
}

# 函数：检查SSH连接
check_ssh_connection() {
    info "检查SSH连接..."
    if ! execute_remote "echo '连接成功'" > /dev/null; then
        error_exit "SSH连接失败，请检查网络和认证信息"
    fi
    success "SSH连接成功"
}

# 函数：检查数据库连接
check_db_connection() {
    info "检查远程数据库连接..."
    if ! execute_remote_query "SELECT 1;" > /dev/null; then
        error_exit "无法连接到远程数据库"
    fi
    success "远程数据库连接成功"
}

# 函数：显示可修改的表列表
show_tables() {
    echo
    info "可修改时间戳的表列表:"
    echo "================================"
    
    local tables=(
        "1) users - 用户表 (created_at)"
        "2) recipe - 食谱表 (created_at, updated_at)"
        "3) recipe_comment - 食谱评论表 (created_at)"
        "4) favorite - 收藏表 (created_at)"
        "5) likes - 点赞表 (created_at)"
        "6) recipe_draft - 草稿表 (created_at, updated_at)"
        "7) user_follow - 用户关注表 (created_at)"
        "8) user_browse - 用户浏览历史表 (created_at)"
        "9) user_message - 用户消息表 (created_at)"
    )
    
    for table in "${tables[@]}"; do
        echo "  $table"
    done
    echo
}

# 函数：获取表信息
get_table_info() {
    local table_num=$1
    case $table_num in
        1) echo "users id created_at";;
        2) echo "recipe id created_at updated_at";;
        3) echo "recipe_comment id created_at";;
        4) echo "favorite user_id recipe_id created_at";;
        5) echo "likes user_id recipe_id created_at";;
        6) echo "recipe_draft id created_at updated_at";;
        7) echo "user_follow follower_id followee_id created_at";;
        8) echo "user_browse id created_at";;
        9) echo "user_message id created_at";;
        *) echo "";;
    esac
}

# 函数：获取总记录数
get_total_records() {
    local table_info="$1"
    local table_info_array=($table_info)
    local table_name=${table_info_array[0]}
    local id_field=${table_info_array[1]}
    
    local query="SELECT COUNT(*) FROM $table_name;"
    local count=$(execute_remote_query "$query")
    echo $count
}

# 函数：显示分页数据
show_table_data_paginated() {
    local table_info="$1"
    local page=$2
    local table_info_array=($table_info)
    local table_name=${table_info_array[0]}
    local id_field=${table_info_array[1]}
    
    # 计算偏移量
    local offset=$(( (page - 1) * PAGE_SIZE ))
    
    echo
    info "表 $table_name 中的数据 (第 $page 页，每页 $PAGE_SIZE 条):"
    echo "=========================================="
    
    # 构建查询字段
    local fields="$id_field"
    for ((i=2; i<${#table_info_array[@]}; i++)); do
        fields="$fields, ${table_info_array[i]}"
    done
    
    local query="SELECT $fields FROM $table_name ORDER BY $id_field DESC LIMIT $PAGE_SIZE OFFSET $offset;"
    local result=$(execute_remote_query "$query")
    
    if [ -z "$result" ]; then
        warning "表中没有数据"
        return 1
    fi
    
    # 显示列名
    echo "编号 | $fields"
    echo "------------------------------------------"
    
    # 显示带编号的数据
    local line_num=1
    local global_num=$((offset + 1))
    while IFS= read -r line; do
        printf "%-4s | %s\n" "$global_num" "$line"
        ((line_num++))
        ((global_num++))
    done <<< "$result"
    
    # 显示分页信息
    echo "------------------------------------------"
    local total_pages=$(( (TOTAL_RECORDS + PAGE_SIZE - 1) / PAGE_SIZE ))
    info "第 $page 页，共 $total_pages 页，总计 $TOTAL_RECORDS 条记录"
    
    return 0
}

# 函数：分页导航
show_pagination_nav() {
    local current_page=$1
    local total_pages=$2
    
    echo
    info "分页导航:"
    echo "  n) 下一页    p) 上一页    g) 跳转到页    s) 选择记录    q) 返回表选择"
    
    if [ $current_page -gt 1 ]; then
        echo -n "  p"
    fi
    if [ $current_page -lt $total_pages ]; then
        echo -n "  n"
    fi
    echo "  g  s  q"
    echo
}

# 函数：处理分页交互
handle_pagination() {
    local table_info="$1"
    local table_info_array=($table_info)
    local table_name=${table_info_array[0]}
    local id_field=${table_info_array[1]}
    
    CURRENT_PAGE=1
    CURRENT_TABLE="$table_name"
    CURRENT_TABLE_INFO="$table_info"
    
    # 获取总记录数
    TOTAL_RECORDS=$(get_total_records "$table_info")
    if [ "$TOTAL_RECORDS" -eq 0 ]; then
        warning "表中没有数据"
        return 1
    fi
    
    local total_pages=$(( (TOTAL_RECORDS + PAGE_SIZE - 1) / PAGE_SIZE ))
    
    while true; do
        # 显示当前页数据
        if ! show_table_data_paginated "$table_info" $CURRENT_PAGE; then
            return 1
        fi
        
        # 显示分页导航
        show_pagination_nav $CURRENT_PAGE $total_pages
        
        read -p "请选择操作: " nav_choice
        
        case $nav_choice in
            n|N)
                if [ $CURRENT_PAGE -lt $total_pages ]; then
                    ((CURRENT_PAGE++))
                else
                    warning "已经是最后一页"
                fi
                ;;
            p|P)
                if [ $CURRENT_PAGE -gt 1 ]; then
                    ((CURRENT_PAGE--))
                else
                    warning "已经是第一页"
                fi
                ;;
            g|G)
                read -p "请输入要跳转的页码 (1-$total_pages): " jump_page
                if [[ "$jump_page" =~ ^[0-9]+$ ]] && [ "$jump_page" -ge 1 ] && [ "$jump_page" -le "$total_pages" ]; then
                    CURRENT_PAGE=$jump_page
                else
                    warning "无效的页码"
                fi
                ;;
            s|S)
                select_record_from_page "$table_info" $CURRENT_PAGE
                if [ $? -eq 0 ]; then
                    return 0
                fi
                ;;
            q|Q)
                info "返回表选择"
                return 1
                ;;
            *)
                warning "无效的选择，请重新输入"
                ;;
        esac
    done
}

# 函数：从当前页选择记录
select_record_from_page() {
    local table_info="$1"
    local page=$2
    local table_info_array=($table_info)
    local table_name=${table_info_array[0]}
    local id_field=${table_info_array[1]}
    
    local offset=$(( (page - 1) * PAGE_SIZE ))
    
    # 对于复合主键表，我们需要获取所有主键字段
    local primary_key_fields=""
    if [[ "$table_name" == "favorite" || "$table_name" == "likes" ]]; then
        primary_key_fields="user_id, recipe_id"
    elif [[ "$table_name" == "user_follow" ]]; then
        primary_key_fields="follower_id, followee_id"
    else
        primary_key_fields="$id_field"
    fi
    
    # 获取当前页的记录（包含所有主键字段）
    local query="SELECT $primary_key_fields FROM $table_name ORDER BY $id_field DESC LIMIT $PAGE_SIZE OFFSET $offset;"
    local record_data=$(execute_remote_query "$query")
    
    if [ -z "$record_data" ]; then
        warning "当前页没有记录"
        return 1
    fi
    
    # 将记录数据存入数组
    local records_array=()
    while IFS= read -r line; do
        records_array+=("$line")
    done <<< "$record_data"
    
    echo
    read -p "请输入要修改的记录编号 (1-${#records_array[@]}): " record_num
    
    if [[ ! "$record_num" =~ ^[0-9]+$ ]] || [ "$record_num" -lt 1 ] || [ "$record_num" -gt "${#records_array[@]}" ]; then
        warning "无效的记录编号"
        return 1
    fi
    
    local selected_index=$((record_num - 1))
    local selected_record=${records_array[$selected_index]}
    
    info "您选择了记录编号 $record_num，记录: $selected_record"
    
    # 验证记录是否存在
    if ! validate_record "$table_info" "$selected_record"; then
        warning "记录 $selected_record 不存在"
        return 1
    fi
    
    # 进入修改时间戳流程
    modify_timestamp "$table_info" "$selected_record"
    return 0
}

# 函数：验证记录是否存在
validate_record() {
    local table_info="$1"
    local table_info_array=($table_info)
    local record_data="$2"
    local table_name=${table_info_array[0]}
    local id_field=${table_info_array[1]}
    
    # 构建WHERE条件 - 处理复合主键
    local where_condition=""
    if [[ "$table_name" == "favorite" || "$table_name" == "likes" ]]; then
        # favorite和likes表使用复合主键 (user_id, recipe_id)
        IFS=',' read -ra id_parts <<< "$record_data"
        if [ ${#id_parts[@]} -lt 2 ]; then
            warning "无效的复合主键格式: $record_data"
            return 1
        fi
        where_condition="user_id = '${id_parts[0]}' AND recipe_id = '${id_parts[1]}'"
    elif [[ "$table_name" == "user_follow" ]]; then
        # user_follow表使用复合主键 (follower_id, followee_id)
        IFS=',' read -ra id_parts <<< "$record_data"
        if [ ${#id_parts[@]} -lt 2 ]; then
            warning "无效的复合主键格式: $record_data"
            return 1
        fi
        where_condition="follower_id = '${id_parts[0]}' AND followee_id = '${id_parts[1]}'"
    else
        # 其他表使用单字段主键
        where_condition="$id_field = '$record_data'"
    fi
    
    local query="SELECT COUNT(*) FROM $table_name WHERE $where_condition;"
    local count=$(execute_remote_query "$query")
    
    if [ "$count" -eq 0 ]; then
        return 1
    fi
    return 0
}

# 函数：显示当前记录详情
show_record_detail() {
    local table_info="$1"
    local table_info_array=($table_info)
    local record_data="$2"
    local table_name=${table_info_array[0]}
    local id_field=${table_info_array[1]}
    
    echo
    info "记录详情:"
    echo "=========================================="
    
    # 构建查询字段
    local fields="$id_field"
    for ((i=2; i<${#table_info_array[@]}; i++)); do
        fields="$fields, ${table_info_array[i]}"
    done
    
    # 构建WHERE条件 - 处理复合主键
    local where_condition=""
    if [[ "$table_name" == "favorite" || "$table_name" == "likes" ]]; then
        # favorite和likes表使用复合主键 (user_id, recipe_id)
        IFS=',' read -ra id_parts <<< "$record_data"
        if [ ${#id_parts[@]} -lt 2 ]; then
            warning "无效的复合主键格式: $record_data"
            return 1
        fi
        where_condition="user_id = '${id_parts[0]}' AND recipe_id = '${id_parts[1]}'"
    elif [[ "$table_name" == "user_follow" ]]; then
        # user_follow表使用复合主键 (follower_id, followee_id)
        IFS=',' read -ra id_parts <<< "$record_data"
        if [ ${#id_parts[@]} -lt 2 ]; then
            warning "无效的复合主键格式: $record_data"
            return 1
        fi
        where_condition="follower_id = '${id_parts[0]}' AND followee_id = '${id_parts[1]}'"
    else
        # 其他表使用单字段主键
        where_condition="$id_field = '$record_data'"
    fi
    
    local query="SELECT $fields FROM $table_name WHERE $where_condition;"
    local result=$(execute_remote_query "$query")
    
    if [ -z "$result" ]; then
        warning "未找到记录详情"
        return 1
    fi
    
    # 显示列名
    echo "$fields"
    echo "------------------------------------------"
    echo "$result"
    return 0
}

# 函数：修改时间戳
modify_timestamp() {
    local table_info="$1"
    local table_info_array=($table_info)
    local record_data="$2"
    local table_name=${table_info_array[0]}
    local id_field=${table_info_array[1]}
    
    # 显示当前记录详情
    if ! show_record_detail "$table_info" "$record_data"; then
        return 1
    fi
    
    # 选择要修改的时间字段
    echo
    info "请选择要修改的时间字段:"
    
    # 确定时间字段的起始索引
    local time_field_start_index=2
    if [[ "$table_name" == "favorite" || "$table_name" == "likes" || "$table_name" == "user_follow" ]]; then
        # 复合主键表：表结构是 [表名, 主键字段1, 主键字段2, 时间字段...]
        time_field_start_index=3  # 跳过表名和两个主键字段
    fi
    
    local time_fields=()
    local choice_num=1
    for ((i=time_field_start_index; i<${#table_info_array[@]}; i++)); do
        time_fields+=("${table_info_array[i]}")
        echo "  $choice_num) ${table_info_array[i]}"
        ((choice_num++))
    done
    
    local max_choice=${#time_fields[@]}
    if [ $max_choice -eq 0 ]; then
        error_exit "该表没有可修改的时间字段"
    fi
    
    echo "  $((max_choice + 1))) 不修改，退出"
    
    read -p "请选择 [1-$((max_choice + 1))]: " time_choice
    
    if [ "$time_choice" -eq $((max_choice + 1)) ]; then
        info "退出修改"
        return
    fi
    
    if [ "$time_choice" -lt 1 ] || [ "$time_choice" -gt $max_choice ]; then
        error_exit "无效的选择"
    fi
    
    local time_field_index=$((time_choice - 1))
    local time_field=${time_fields[$time_field_index]}
    
    if [ -z "$time_field" ]; then
        error_exit "无效的时间字段"
    fi
    
    # 选择时间设置方式
    echo
    info "请选择时间设置方式:"
    echo "  1) 使用系统当前时间"
    echo "  2) 手动输入时间"
    echo "  3) 取消修改"
    
    read -p "请选择 [1-3]: " time_method
    
    case $time_method in
        1)
            new_time="CURRENT_TIMESTAMP"
            display_time="系统当前时间"
            ;;
        2)
            read -p "请输入时间 (格式: YYYY-MM-DD HH:MI:SS): " manual_time
            if [[ ! "$manual_time" =~ ^[0-9]{4}-[0-9]{2}-[0-9]{2}\ [0--9]{2}:[0-9]{2}:[0-9]{2}$ ]]; then
                error_exit "时间格式不正确，请使用: YYYY-MM-DD HH:MI:SS"
            fi
            new_time="'$manual_time'"
            display_time="$manual_time"
            ;;
        3)
            info "取消修改"
            return
            ;;
        *)
            error_exit "无效的选择"
            ;;
    esac
    
    # 构建WHERE条件 - 处理复合主键
    local where_condition=""
    if [[ "$table_name" == "favorite" || "$table_name" == "likes" ]]; then
        IFS=',' read -ra id_parts <<< "$record_data"
        where_condition="user_id = '${id_parts[0]}' AND recipe_id = '${id_parts[1]}'"
    elif [[ "$table_name" == "user_follow" ]]; then
        IFS=',' read -ra id_parts <<< "$record_data"
        where_condition="follower_id = '${id_parts[0]}' AND followee_id = '${id_parts[1]}'"
    else
        where_condition="$id_field = '$record_data'"
    fi
    
    # 确认修改
    echo
    warning "即将执行以下修改:"
    echo "  表: $table_name"
    echo "  记录: $record_data"
    echo "  字段: $time_field"
    echo "  新时间: $display_time"
    echo
    
    read -p "确认修改? (y/N): " confirm
    if [[ ! "$confirm" =~ ^[Yy]$ ]]; then
        info "修改已取消"
        return
    fi
    
    # 执行修改
    local update_query="UPDATE $table_name SET $time_field = $new_time WHERE $where_condition;"
    info "执行SQL: $update_query"
    
    if execute_remote_command "$update_query"; then
        success "时间戳修改成功"
        
        # 显示修改后的结果
        info "修改后的记录详情:"
        show_record_detail "$table_info" "$record_data"
    else
        error_exit "修改失败"
    fi
}

# 主函数
main() {
    echo
    echo "========================================"
    echo "   远程数据库时间戳修改工具"
    echo "   服务器: $REMOTE_HOST"
    echo "   数据库: $DB_NAME"
    echo "========================================"
    
    # 检查依赖
    if ! command -v sshpass &> /dev/null; then
        error_exit "请先安装 sshpass"
    fi
    
    # 检查连接
    check_ssh_connection
    check_db_connection
    
    while true; do
        show_tables
        
        read -p "请选择要操作的表 (1-9, 0退出): " table_choice
        
        if [ "$table_choice" -eq 0 ]; then
            info "已退出"
            break
        fi
        
        if [ "$table_choice" -lt 1 ] || [ "$table_choice" -gt 9 ]; then
            warning "无效输入"
            continue
        fi
        
        table_info=$(get_table_info "$table_choice")
        if [ -z "$table_info" ]; then
            warning "无效选择"
            continue
        fi
        
        # 进入分页浏览模式
        handle_pagination "$table_info"
        
        echo
        read -p "是否继续修改其他表? (y/N): " continue_choice
        if [[ ! "$continue_choice" =~ ^[Yy]$ ]]; then
            info "已退出"
            break
        fi
    done
}

main "$@"